-- =============================================
-- This stored procedure updates SortOrder for current content records
-- that is contained in a specified nav menu or content folder.
--
-- Modifications
-- 09/11/2003    E.Tatsui     Created
-- 05/03/2004    E.Tatsui     Removed lines to reset WorkflowStatusCode to W and PublishedDateTime to NULL
--                            for Nav_Menu because it was causing a problem for amsp_CMDeleteContent.
--                            Reset logic seemed to be redundant anyway.
-- =============================================

CREATE  PROCEDURE [dbo].[amsp_CMRenumCurrentContent]
  @InNavMenuID numeric
AS
BEGIN
  DECLARE
    @Counter int,
    @ContentID numeric,
    @SortOrder numeric

  DECLARE c_Contents CURSOR FOR
   SELECT ContentID,
          SortOrder
     FROM vCurrent_Content
    WHERE NavMenuID = @InNavMenuID
    ORDER BY SortOrder

  SET @Counter = 0

  OPEN c_Contents
  FETCH NEXT FROM c_Contents
   INTO @ContentID,
        @SortOrder

  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @Counter = @Counter + 1

    IF @Counter <> @SortOrder BEGIN
      UPDATE Content
         SET SortOrder = @Counter
       WHERE ContentID = @ContentID
      -- If this item is changing to 1st Content, make it a default content.
      -- Also mark the navigation item as "Working" and reset publish date, so that red flag
      -- will show up to notify the administrator to re-publish.
      IF @Counter = 1
        UPDATE Nav_Menu
           SET ContentID = @ContentID,
         WorkflowStatusCode = 'W',
         PublishedDateTime = NULL,
         ComponentCode = 'CM',
         ComponentScriptCode = 'SC'
         WHERE NavMenuID = @InNavMenuID
    END

    FETCH NEXT FROM c_Contents
     INTO @ContentID,
          @SortOrder
  END
  CLOSE c_Contents
  DEALLOCATE c_Contents

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMRenumCurrentContent] TO [IMIS]
GO